/**
 * 2017年10月24日 星期二
 * 财务系统/动态生成报表
  * >> param
    * @v_uid 用户代码
    * @v_no 编号， 2017/ 201710


    >> 使用系统函数
        char_length     计算长度
        str_to_date

    >> 查询测试
        call finance_creport_sp('2', '2017');
 */

 

drop procedure if exists `finance_creport_sp`; -- 同同时执行报错
delimiter //
create procedure `finance_creport_sp`(
    v_uid   int,
    v_no    varchar(10)
)
this_sp:begin
    declare v_count int default 0;                  -- 中账单统计数
    declare v_incount int default 0;                -- 总资流出统计
    declare v_pure_count int default 0;             -- 净资金数
    declare v_pure_incount int default 0;           -- 净资流入统计
    declare v_pure_outcount int default 0;          -- 净资流出统计
    declare v_money numeric(12, 3) default 0;       -- 总流水账单
    declare v_income numeric(12, 3) default 0;      -- 总收入
    declare v_pure_money numeric(12, 3) default 0;  -- 净资金流账单
    declare v_pure_income numeric(12, 3) default 0; -- 净总收入
    declare v_pure_outcome numeric(12, 3) default 0;-- 净总支出
    declare v_date1 date;                           -- 起始日期
    declare v_date2 date;                           -- 截止日期

    declare v_date_mk boolean default false;

    -- :) 日期处理
    -- 年度账单 2017
    if char_length(v_no) = 4 then
        set v_date1 = str_to_date(concat(v_no, '-01-01'), '%Y-%m-%d');
        set v_date2 = date_add(str_to_date(concat(v_no*1+1, '-01-01'), '%Y-%m-%d'), INTERVAL -1 DAY);
        set v_date_mk = true;
    -- 月度账单 201701
    elseif char_length(v_no) = 6 then
        set v_date1 = str_to_date(concat(v_no, '01'), '%Y%m%d');
        set v_date2 = date_add(date_add(v_date1, INTERVAL 1 MONTH), INTERVAL -1 DAY);
        set v_date_mk = true;
    end if;

    -- 日期解析错误跳出存储过程
    if v_date_mk = false then
        leave this_sp;
    end if;

    -- 数据总数计算
    select count(*) into v_count from `fnc1000c` where `uid`= v_uid and `date`>= v_date1 and `date` <= v_date2;
    -- 资金流水
    select ifnull(sum(`money`), 0) into v_money from `fnc1000c` where `uid`= v_uid and `date`>= v_date1 and `date` <= v_date2;
    select ifnull(sum(`money`), 0) into v_income from `fnc1000c` where `uid`= v_uid and `date`>= v_date1 and `date` <= v_date2 and `type`='IN';
    -- 净资金流
    /**
        select if(a.`type` = 'OU', 0-a.`money`, a.`money`) as `money`, a.* 
        from `fnc1000c` a join `fnc0020c` b on a.`master_id`=b.`listid` 
        left join `fnc0020c` c on a.`slave_id`=c.`listid` where 
            c.`type` is null or b.`type`<>c.`type` 
        order by a.`date`
        ;
        -- 仅仅 b.`type`<>c.`type` 会排除 c.`type` 为 null 的条件
    **/
    -- count 统计无效
    select
        ifnull(sum(`money`), 0), ifnull(sum(`income`), 0), ifnull(sum(`outcome`), 0),
        count(*)
            /*, count(`income`), count(`outcome`)*/
            into 
        v_pure_money, v_pure_income, v_pure_outcome,
        v_pure_count    /*, v_pure_incount, v_pure_outcount*/
    from (
        select 
            if(a.`type` = 'OU', 0-a.`money`, a.`money`) as `money`,
            if(a.`type` = 'OU', a.`money`, 0) as `outcome`,
            if(a.`type` = 'IN', a.`money`, 0) as `income`
        from `fnc1000c` a
            join `fnc0020c` b on a.`master_id`=b.`listid`
            left join `fnc0020c` c on a.`slave_id`=c.`listid`
            where 
                a.`uid`= v_uid and 
                a.`date`>= v_date1 and 
                a.`date` <= v_date2 and 
                (c.`type` is null or b.`type`<>c.`type`) 
    ) tt 
    ;          
    select count(*) into v_pure_incount
    from `fnc1000c` a
        join `fnc0020c` b on a.`master_id`=b.`listid`
        left join `fnc0020c` c on a.`slave_id`=c.`listid`
        where                         
            a.`type` = 'IN' and 
            a.`uid`= v_uid and 
            a.`date`>= v_date1 and 
            a.`date` <= v_date2 and 
            (c.`type` is null or b.`type`<>c.`type`)
    ;        
    
    set v_pure_outcount = v_pure_count - v_pure_incount;

    -- 返回结果
    select 
        v_count as `count`,
        v_incount as `incount`,
        (v_count - v_incount) as `outcount`,
        v_pure_count as `pure_count`,
        v_pure_incount as `p_incount`,
        v_pure_outcount as `p_outcount`,
        v_pure_money as `pure_money`,
        v_pure_income as `p_income`,
        v_pure_outcome as `p_outcome`,
        v_money as `money`,
        v_income as `income`,
        (v_money - v_income) as `outcome`,
        v_date1 as `date1`,
        v_date2 as `date2`
    ;
end;